import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  // 创建健康数据表
  await knex.schema.createTable('health_data', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(lower(hex(randomblob(4))) || \'-\' || lower(hex(randomblob(2))) || \'-4\' || substr(lower(hex(randomblob(2))),2) || \'-\' || substr(\'89ab\',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || \'-\' || lower(hex(randomblob(6))))'));
    table.uuid('user_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
    table.date('record_date').notNullable();
    table.integer('steps').defaultTo(0);
    table.decimal('distance', 8, 2).defaultTo(0).comment('距离，单位：km');
    table.integer('calories').defaultTo(0).comment('卡路里消耗');
    table.integer('active_minutes').defaultTo(0).comment('活跃分钟数');
    table.json('heart_rate_data').nullable().comment('心率数据数组');
    table.integer('avg_heart_rate').nullable().comment('平均心率');
    table.integer('max_heart_rate').nullable().comment('最大心率');
    table.integer('min_heart_rate').nullable().comment('最小心率');
    table.integer('stress_level').nullable().comment('压力水平 1-100');
    table.string('data_source', 50).defaultTo('huawei_watch').comment('数据来源');
    table.timestamp('synced_at').nullable().comment('同步时间');
    table.timestamps(true, true);
    
    // 联合唯一索引
    table.unique(['user_id', 'record_date']);
    
    // 其他索引
    table.index(['user_id']);
    table.index(['record_date']);
    table.index(['data_source']);
  });

  // 创建睡眠数据表
  await knex.schema.createTable('sleep_data', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(lower(hex(randomblob(4))) || \'-\' || lower(hex(randomblob(2))) || \'-4\' || substr(lower(hex(randomblob(2))),2) || \'-\' || substr(\'89ab\',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || \'-\' || lower(hex(randomblob(6))))'));
    table.uuid('user_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
    table.date('sleep_date').notNullable().comment('睡眠日期');
    table.timestamp('bedtime').notNullable().comment('上床时间');
    table.timestamp('sleep_start').notNullable().comment('入睡时间');
    table.timestamp('wake_time').notNullable().comment('醒来时间');
    table.timestamp('get_up_time').notNullable().comment('起床时间');
    table.integer('total_duration').notNullable().comment('总睡眠时长(分钟)');
    table.integer('deep_sleep').defaultTo(0).comment('深度睡眠时长(分钟)');
    table.integer('light_sleep').defaultTo(0).comment('浅度睡眠时长(分钟)');
    table.integer('rem_sleep').defaultTo(0).comment('REM睡眠时长(分钟)');
    table.integer('awake_duration').defaultTo(0).comment('清醒时长(分钟)');
    table.decimal('sleep_efficiency', 5, 2).nullable().comment('睡眠效率百分比');
    table.integer('sleep_score').nullable().comment('睡眠得分 0-100');
    table.integer('wake_up_count').defaultTo(0).comment('醒来次数');
    table.json('sleep_stages').nullable().comment('睡眠阶段详细数据');
    table.string('data_source', 50).defaultTo('huawei_watch');
    table.timestamp('synced_at').nullable();
    table.timestamps(true, true);
    
    // 联合唯一索引
    table.unique(['user_id', 'sleep_date']);
    
    // 其他索引
    table.index(['user_id']);
    table.index(['sleep_date']);
    table.index(['sleep_score']);
  });

  // 创建运动记录表
  await knex.schema.createTable('exercise_records', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(lower(hex(randomblob(4))) || \'-\' || lower(hex(randomblob(2))) || \'-4\' || substr(lower(hex(randomblob(2))),2) || \'-\' || substr(\'89ab\',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || \'-\' || lower(hex(randomblob(6))))'));
    table.uuid('user_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
    table.string('exercise_type', 50).notNullable().comment('运动类型');
    table.timestamp('start_time').notNullable();
    table.timestamp('end_time').notNullable();
    table.integer('duration').notNullable().comment('运动时长(分钟)');
    table.integer('calories_burned').defaultTo(0);
    table.decimal('distance', 8, 2).defaultTo(0).comment('运动距离(km)');
    table.integer('avg_heart_rate').nullable();
    table.integer('max_heart_rate').nullable();
    table.json('heart_rate_zones').nullable().comment('心率区间分布');
    table.decimal('avg_pace', 8, 2).nullable().comment('平均配速(分钟/km)');
    table.json('gps_data').nullable().comment('GPS轨迹数据');
    table.json('performance_metrics').nullable().comment('运动表现指标');
    table.string('data_source', 50).defaultTo('huawei_watch');
    table.timestamp('synced_at').nullable();
    table.timestamps(true, true);
    
    // 索引
    table.index(['user_id']);
    table.index(['exercise_type']);
    table.index(['start_time']);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('exercise_records');
  await knex.schema.dropTableIfExists('sleep_data');
  await knex.schema.dropTableIfExists('health_data');
}